package com.ba360.portal.util;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import com.ba360.portal.database.DBConnectionImpl;

public class Helper {

		Connection conn = null;

		public Helper () {
			DBConnectionImpl dbConn = new DBConnectionImpl();
			try {
				conn = dbConn.getConnection();
			} catch (Exception excp) {
				System.out.println(excp);
				Log.info(excp.getMessage());
			}
		}

		public void Finalize() {
			try {
				conn.close();
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			}
		}
		public String getOrgPath(String realPath, String id, int index) {
			String path = null;
			String[] files;
			String tmpPath = realPath + "/" + id + "/" + index;
			File dir = new File(tmpPath);
		  	if(dir.isDirectory()) {
				files = dir.list();
				if(files!=null && files.length > 0) {
					tmpPath += "/" + files[0];
					if(files[0]!=null && (new File(tmpPath)).isFile())
						
						path="http://www.droik.com/droik/Users/images/"+ realPath.substring(realPath.lastIndexOf("/")+1,realPath.length())+ "/"+id+"/"+index+"/"+files[0];						
				}
		  	}
			return path;
		}

		public int getUserId(String advId) {

			Statement stmt = null;
			int memId = 0;
			ResultSet rs = null;

			try {

				stmt = conn.createStatement();

				String statement = "select * from ads where ad_id=" + advId + " order by date_posted desc";
				rs = stmt.executeQuery(statement);

				if(rs.next()) {
					memId = rs.getInt("member_id");
				}
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					rs.close();
					stmt.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}

			return memId;
		}

		public String getComment(int id, String advId) {

			Statement stmt = null;
			ResultSet rs = null;
			String comment = "";

			try {
				stmt = conn.createStatement();

				String statement = "select comment" + id + " from ads where ad_id=" + advId + " order by date_posted desc";
				rs = stmt.executeQuery(statement);

				if(rs.next()) {
					comment = rs.getString("comment"+id);
				}
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					rs.close();
					stmt.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}

			return comment;
		}

		public void deleteImg(String realPath, String id, int index) {
			File dir = new File(realPath + "\\" + id + "\\" + index);
			if (dir.isDirectory()) {
				File[] files = dir.listFiles();
				for (int i = 0; i < files.length; i++) {
					files[i].delete();
				}
			}
			dir.delete();
		}

		public void deleteComt(String id, int index) {

			Statement stmt = null;

			try {
				stmt = conn.createStatement();

				String statement = "update ads set comment" + index + " = '' where ad_id=" + id + " order by date_posted desc";
				stmt.execute(statement);

			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					stmt.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}
		}

		public int getHeadCount(String headName) {

			Statement stmt1 = null;
			Statement stmt2 = null;
			ResultSet rs1 = null;
			ResultSet rs2 = null;
			int count = 0;
			int id = 0;

			try {
				stmt1 = conn.createStatement();
				stmt2 = conn.createStatement();

				// Get the current date.
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Calendar today = Calendar.getInstance();
				// move to 100 days back
				today.add(Calendar.DAY_OF_YEAR, -100);


				String statement = "select category_id from categories where name = '" + headName +"'";
				rs1 = stmt1.executeQuery(statement);
				if(rs1.next()) {
					id = rs1.getInt("category_id");

					//select count(*) tot from ads where date_posted > '"+ sdf.format(today.getTime()) +"' and category_id = (select category_id from categories where par_category_id = (select category_id from categories where name = '" + headName +"'))

					statement = "select count(*) tot from ads,categories ca where ads.date_posted > '"+ sdf.format(today.getTime()) +"' and ads.category_id = ca.category_id and ca.par_category_id = " + id;
					rs2 = stmt2.executeQuery(statement);

					if(rs2.next()) {
						count = rs2.getInt("tot");
					}
				}
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					rs1.close();
					if (rs2 != null)
						rs2.close();
					stmt1.close();
					if (stmt2 != null)
						stmt2.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}

			return count;
		}

		public int getSubHeadCount(String subHeadName) {

			Statement stmt = null;
			ResultSet rs = null;
			int count = 0;

			try {
				stmt = conn.createStatement();
				// Get the current date.
				SimpleDateFormat sdf = new SimpleDateFormat ( "yyyy-MM-dd" ) ;
				Calendar today = Calendar.getInstance();
				// move to 250 days back
				today.add ( Calendar.DAY_OF_YEAR, -250 );
				String statement = "select count(*) tot from ads where date_posted > '"+ sdf.format(today.getTime()) +"' and category_id in (select category_id from categories where name = '" + subHeadName +"')";
				rs = stmt.executeQuery(statement);

				if(rs.next()) {
					count = rs.getInt("tot");
				}
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					rs.close();
					stmt.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}

			return count;
		}

		public int updateHits(String advId) {

			Statement stmt = null;
			ResultSet rs = null;
			int hits = 0;

			try {
				stmt = conn.createStatement();
				String statement = "select hits from ads where ad_id=" + advId + " order by date_posted desc";
				rs = stmt.executeQuery(statement);

				if(rs.next()) {
					hits = rs.getInt("hits");
				}
				hits++;
				statement = "update ads set hits = '"+ hits +"' where ad_id=" + advId + " order by date_posted desc";
				stmt.execute(statement);

			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			} finally {
				try {
					stmt.close();
				} catch (SQLException SQLExcp) {
					System.out.println(SQLExcp);
					Log.info(SQLExcp.getMessage());
				}
			}
			return hits;
		}
}